﻿Imports System.Data.OleDb

Public Class LopHocDAO
    Dim sDBName As String = "Database\thietkethoikhoabieu.mdb"

    Public Function LayDanhSachLopHoc() As List(Of LopHocDTO)
        Dim str = "SELECT * FROM LopHoc"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of LopHocDTO)

        While reader.Read()
            Dim lh As New LopHocDTO
            lh.MaLop = reader.GetString(0)
            lh.TenLop = reader.GetString(1)
            lh.Khoi = reader.GetString(2)

            ds.Add(lh)
        End While

        conn.Close()
        Return ds
    End Function

    Public Function ThemLopHoc(ByVal lh As LopHocDTO) As Integer
        Dim str = String.Format("INSERT INTO LopHoc(MaLop, TenLop, Khoi) " _
                & "values ('{0}','{1}','{2}')", lh.MaLop, lh.TenLop, lh.Khoi)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

    Public Function XoaLopHoc(ByVal lh As LopHocDTO) As Integer
        Dim str = "DELETE FROM LopHoc WHERE MaLop='" & lh.MaLop & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function SuaLopHoc(ByVal lh As LopHocDTO) As Integer
        Dim str = String.Format("UPDATE LopHoc SET TenLop='{0}', Khoi='{1}' WHERE MaLop='{2}'" _
                                , lh.TenLop, lh.Khoi, lh.MaLop)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function TraCuuLopHoc(ByVal lh As LopHocDTO) As List(Of LopHocDTO)
        Dim sSelect = String.Format("SELECT * FROM LopHoc WHERE MaLop LIKE '%{0}%' AND " _
                    & "TenLop LIKE '%{1}%' AND Khoi LIKE '%{2}%' ", lh.MaLop, lh.TenLop, lh.Khoi)

        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of LopHocDTO)

        While reader.Read()
            Dim rlh As New LopHocDTO
            rlh.MaLop = reader.GetString(0)
            rlh.TenLop = reader.GetString(1)
            rlh.Khoi = reader.GetString(2)

            ds.Add(rlh)
        End While

        conn.Close()
        Return ds

    End Function
End Class
